package com.ivo.service.dcc;

import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.ivo.dao.HibernateCaller;
import com.ivo.model.dcc.Attachment;
import com.ivo.model.dcc.DocList;
import com.ivo.model.dcc.Document;
import com.ivo.model.dcc.DocumentAttachment;
import com.ivo.model.dcc.DocumentRequisition;
import com.ivo.model.dcc.DocumentVersion;
import com.ivo.model.sys.ApprovalRecord;
import com.ivo.model.sys.OrderTask;

@Service
public class DccService
{
	@Autowired
	private HibernateCaller hibernateCaller;

	/**
	 * Sheet
	 */
	public Document getDocument(Long document_ID)
	{
		Document document = (Document) hibernateCaller.getObject( Document.class, document_ID );
		return document;
	}

	public Document getDocumentByNumber(String documentNumber)
	{
		String hql = "from Document d where d.documentNumber = ?  ";
		List list = hibernateCaller.find( hql, documentNumber );
		Document document = (Document) list.get( 0 );
		return document;
	}

	public DocumentVersion getDocumentVersion(Long documentVersion_ID)
	{
		DocumentVersion documentVersion = (DocumentVersion) hibernateCaller.getObject( DocumentVersion.class, documentVersion_ID );
		return documentVersion;
	}

	public DocumentRequisition getDocumentRequisition(Long documentRequisitionId)
	{
		DocumentRequisition documentRequisition = (DocumentRequisition) hibernateCaller.getObject( DocumentRequisition.class, documentRequisitionId );
		return documentRequisition;
	}

	public OrderTask getOrderTask(String orderTask_ID)
	{
		OrderTask orderTask = (OrderTask) hibernateCaller.getObject( OrderTask.class, orderTask_ID );
		return orderTask;
	}

	public DocumentRequisition getDocumentRequisitionInstance(String trackingNumber)
	{
		String hql = "from DocumentRequisition d where d.trackingNumber = ? and d.validFlag = 1 ";
		List list = hibernateCaller.find( hql, trackingNumber );
		DocumentRequisition documentRequisition = (DocumentRequisition) list.get( 0 );
		return documentRequisition;
	}

	public DocumentRequisition getDocumentRequisitionByNumber(String documentNumber)
	{
		String hql = "from DocumentRequisition d where d.documentNumber = ? and d.orderTask_FK not in ('990','000','980') and d.validFlag = 1 ";
		List list = hibernateCaller.find( hql, documentNumber );
		DocumentRequisition documentRequisition = null;
		if( list.size() > 0 ) documentRequisition = (DocumentRequisition) list.get( 0 );
		return documentRequisition;
	}

	public DocList getDocListByNumber(String documentNumber)
	{
		String hql = " from DocList d where d.documentNumber = ? ";
		List list = hibernateCaller.find( hql, documentNumber );
		DocList docList = null;
		if( list.size() > 0 ) docList = (DocList) list.get( 0 );
		return docList;
	}

	public DocList getDocList(String documentNumber)
	{
		DocList docList = (DocList) hibernateCaller.getObject( DocList.class, documentNumber );
		return docList;
	}

	public DocumentVersion getCurrentVersion(Long document_ID)
	{
		String hql = "from DocumentVersion d where d.document_FK = ? and d.stateFlag = 1 and d.validFlag = 1 ";
		List list = hibernateCaller.find( hql, document_ID );
		DocumentVersion documentVersion = (DocumentVersion) list.get( 0 );
		return documentVersion;
	}

	public DocumentVersion getCurrentVersionByNumber(String documentNumber)
	{
		Document document = this.getDocumentByNumber( documentNumber );
		String hql = "from DocumentVersion d where d.document_FK = ? and d.stateFlag = 1 and d.validFlag = 1 ";
		List list = hibernateCaller.find( hql, document.getDocument_ID() );
		DocumentVersion documentVersion = (DocumentVersion) list.get( 0 );
		return documentVersion;
	}

	public List<DocumentVersion> getVersionsByDocument(Long document_ID)
	{
		String hql = "from DocumentVersion d where d.document_FK = ? ";
		List list = hibernateCaller.find( hql, document_ID );
		return list;
	}

	public List<DocumentAttachment> getIssuedDocAttachments(String documentNumber)
	{
		DocumentVersion documentVersion = this.getCurrentVersionByNumber( documentNumber );
		String hql = "from DocumentAttachment a where a.documentVersion_FK = ? and a.typeFlag = 1 ";
		List list = hibernateCaller.find( hql, documentVersion.getDocumentVersion_ID() );
		return list;
	}

	public List<Attachment> getSignDocAttachments(String documentNumber)
	{
		String hql = "SELECT Attachment_ID,OrderNumber,FileName,FileSize,ValidFlag FROM SYS_M_Attachment WHERE OrderNumber IN ( SELECT TrackingNumber FROM DCC_M_DocumentRequisition WHERE DocumentNumber LIKE ? AND OrderTask_FK NOT IN ('990','980','000') AND ValidFlag = 1 ) AND ValidFlag = 1";
		List list = hibernateCaller.sqlQuery( hql, Attachment.class, documentNumber );
		return list;
	}

	public List<DocList> docSearch(int index, int maxResult, String condition)
	{
		String hql = "from DocList a where a.documentNumber like ? or a.documentName like ? or a.requisitioner like ? order by a.documentNumber";
		List list = hibernateCaller.findPage( hql, index, maxResult, "%" + condition + "%", "%" + condition + "%", "%" + condition + "%" );
		return list;
	}

	public int getResultSize(String condition)
	{
		String hql = "FROM DCC_V_RD_DocumentList DL WHERE DL.DocumentNumber LIKE '%" + condition + "%' OR DL.DocumentName LIKE '%" + condition + "%' OR DL.Requisitioner LIKE '%" + condition + "%'";
		int size = hibernateCaller.getSize( hql );
		return size;
	}

	public boolean bAuthToReadDoc(String docNumber, String userId, String docStatus)
	{
		StringBuffer sbSQL = new StringBuffer();
		sbSQL.append( " EXEC DCC_SP_DocReadAuth '" );
		sbSQL.append( docNumber );
		sbSQL.append( "','" );
		sbSQL.append( userId );
		sbSQL.append( "','" );
		sbSQL.append( docStatus );
		sbSQL.append( "' " );

		List list = hibernateCaller.sqlGetSingleResult( sbSQL.toString(), "result" );
		String result = (String) list.get( 0 );

		return result.equals( "ok" );
	}

	public boolean bDccSpecialPermisstions(String userId, String documentType_FK)
	{
		StringBuffer sbSQL = new StringBuffer();
		sbSQL.append( " with CTE(DocumentType_ID,DocumentTypeName,DocumentTypeName_Intl,ParementType_FK,ValidFlag,Creator,Updater,DateOfCreate,DateOfUpdate,Memo) as ( " );
		sbSQL.append( " select DocumentType_ID, DocumentTypeName, DocumentTypeName_Intl, ParementType_FK,ValidFlag,Creator,Updater,DateOfCreate,DateOfUpdate,Memo " );
		sbSQL.append( " from DCC_O_DocumentType " );
		sbSQL.append( " where DocumentType_ID IN ( " );
		sbSQL.append( " SELECT WLR.DocumentType_FK FROM DCC_O_WhiteList AS WL " );
		sbSQL.append( " INNER JOIN DCC_D_WhiteListResource AS WLR " );
		sbSQL.append( " ON WL.WhiteList_ID = WLR.WhiteList_FK AND WLR.ValidFlag = 1 AND WL.ValidFlag = 1 " );
		sbSQL.append( " AND WL.Employee_FK = '" + userId + "' " );
		sbSQL.append( " AND WLR.DocumentType_FK IS NOT NULL " );
		sbSQL.append( " ) union all " );
		sbSQL.append( " select DT.DocumentType_ID, DT.DocumentTypeName, DT.DocumentTypeName_Intl, DT.ParementType_FK,DT.ValidFlag,DT.Creator,DT.Updater,DT.DateOfCreate,DT.DateOfUpdate,DT.Memo " );
		sbSQL.append( " from DCC_O_DocumentType as DT inner join CTE " );
		sbSQL.append( " on CTE.DocumentType_ID = DT.ParementType_FK and DT.ValidFlag = 1 " );
		sbSQL.append( " ) " );
		sbSQL.append( " select COUNT(*) AS NUM from CTE WHERE DocumentType_ID = '" + documentType_FK + "' " );

		List list = hibernateCaller.sqlGetSingleResult( sbSQL.toString(), "NUM" );
		String result = (String) list.get( 0 );

		int num = Integer.parseInt( result );

		return ( num > 0 );
	}

	/*
	 * public List<ApprovalRecord> getApprovalRecords(String orderNumber) {
	 * String sql = " SELECT
	 * ApprovalRecord_ID,Parent_FK,OrderNumber,Screen_FK,SendDept_FK,Sender_FK,HandDept_FK,Handler_FK,Delegate_FK,DelegateDept_FK,OrderTask_FK,Transition,DateOfBegin,DateOfEnd,ResultCode,Comment,Visible,Count,HoldFlag ";
	 * sql += " FROM SYS_M_ApprovalRecord WHERE OrderNumber = ? AND Visible = 1
	 * AND ValidFlag = 1"; List list = hibernateCaller.sqlQuery(sql,
	 * ApprovalRecord.class ,orderNumber); return list; }
	 */

	public List<ApprovalRecord> getApprovalRecords(String orderNumber)
	{
		StringBuffer sbSQL = new StringBuffer();
		sbSQL.append( " SELECT AR.ApprovalRecord_ID, AR.Screen_FK, AR.Parent_FK, AR.OrderNumber, AR.SendDept_FK, " );
		sbSQL.append( " D1.DeptName+' '+D1.DeptName_S +'<br/>'+ E1.EmployeeName+' '+E1.EmployeeName_EN AS Sender_FK, AR.HandDept_FK, " );
		sbSQL.append( " D2.DeptName+' '+D2.DeptName_S +'<br/>'+ E2.EmployeeName+' '+E2.EmployeeName_EN AS Handler_FK, " );
		sbSQL.append( " ISNULL(TBL.DeptName+' '+TBL.DeptName_S +'<br/>'+TBL.EmployeeName+' '+TBL.EmployeeName_EN, '') AS Delegate_FK, " );
		sbSQL.append( " AR.DelegateDept_FK,AR.OrderTask_FK, " );
		sbSQL.append( " AR.Transition,AR.DateOfBegin,AR.DateOfEnd,AR.ResultCode,AR.Comment,AR.Count,AR.Visible,AR.HoldFlag " );
		sbSQL.append( " FROM SYS_M_ApprovalRecord AS AR INNER JOIN HR_O_Employee AS E1 " );
		sbSQL.append( " ON AR.Sender_FK = E1.Employee_ID " );
		sbSQL.append( " AND AR.OrderNumber = ? AND AR.Visible = 1 AND AR.ValidFlag = 1 " );
		sbSQL.append( " INNER JOIN HR_O_Department AS D1 ON AR.SendDept_FK = D1.Department_ID " );
		sbSQL.append( " INNER JOIN HR_O_Employee AS E2 ON AR.Handler_FK = E2.Employee_ID " );
		sbSQL.append( " INNER JOIN HR_O_Department AS D2 ON AR.HandDept_FK = D2.Department_ID " );
		sbSQL.append( " LEFT JOIN ( " );
		sbSQL.append( " SELECT E3.Employee_ID,E3.EmployeeName,E3.EmployeeName_EN,D3.Department_ID,D3.DeptName,D3.DeptName_S " );
		sbSQL.append( " FROM HR_O_Employee AS E3 INNER JOIN HR_O_Department AS D3 ON E3.Department_FK = D3.Department_ID " );
		sbSQL.append( " ) AS TBL " );
		sbSQL.append( " ON AR.Delegate_FK = TBL.Employee_ID " );

		List list = hibernateCaller.sqlQuery( sbSQL.toString(), ApprovalRecord.class, orderNumber );
		return list;
	}

	public ApprovalRecord getDeputyApprovalRecord(Long parent_FK, String orderNumber, String handler_FK)
	{
		ApprovalRecord approvalRecord = null;
		String sql = " from ApprovalRecord a where a.parent_FK = ? and a.orderNumber = ? and a.handler_FK = ? ";
		List list = hibernateCaller.get( sql, parent_FK, orderNumber, handler_FK );

		if( list.size() > 0 ) approvalRecord = (ApprovalRecord) list.get( 0 );

		return approvalRecord;
	}

	public OrderTask getOrderTaskCHS(String orderTask)
	{
		OrderTask objOrderTask = null;
		String sql = " SELECT OT.OrderTask_ID,OT.OrderTaskName,ISNULL(D.Mapword, OT.OrderTaskName) AS Memo ";
		sql += " FROM SYS_O_OrderTask AS OT LEFT JOIN SYS_R_Dictionary AS D ";
		sql += " ON OT.OrderTaskName = D.KeyWord_FK AND OT.ValidFlag = 1 AND D.ValidFlag = 1 AND Language_FK = 'CHS' ";
		sql += " WHERE OT.OrderTask_ID = ? ";
		List list = hibernateCaller.sqlQuery( sql, OrderTask.class, orderTask );

		if( list.size() > 0 ) objOrderTask = (OrderTask) list.get( 0 );
		return objOrderTask;
	}

}
